Upgrade öncesinde oracle 12c binary kurulumunu dbua ile upgrade’ i anlattığım aşağıdaki linkde zaten belirtmiştim. Dolayısıyla burda direk upgrade işlemini yapıyor olacağız.

http://www.kamilturkyilmaz.com/2015/06/27/oracle-upgrade-11g-to-12c-dbua/

Upgrade sırasında loglarımızın tek bir dizine çıkması için bir dizin create edip ordan devam edelim;

[oracle@Redhat70 dbs]$ mkdir -p /u01/12c_upgrade
[oracle@Redhat70 dbs]$ cd /u01/12c_upgrade/
[oracle@Redhat70 12c_upgrade]$

Manuel upgrade yapacağımız için ve burda user-friendly bir arayüzümüzde olmadığından upgrade öncesi kontroller son derece kritik önem arzediyor. Bunun için 12c binarysi altında bulunan iki tane sql file’ imiz var onları kullanıyor olacağız. Öncelikle bu iki dosyayı create ettiğimiz dizine kopyalayalım;

[oracle@Redhat70 dbs]$ cp /u01/app/oracle/db/12.1.0.2/rdbms/admin/preupgrd.sql /u01/12c_upgrade/.
[oracle@Redhat70 dbs]$ cp /u01/app/oracle/db/12.1.0.2/db_1/rdbms/admin/utluppkg.sql /u01/12c_upgrade/.

Source oracle_home altından database’ e sys ile bağlanıp preupgrd.sql ‘ i çalıştıracağız. Utluppkg.sql’ ile ilk sql kullandığından dolayı onuda almak durumunda kaldık.

[oracle@Redhat70 12c_upgrade]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 6 22:17:45 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool preupgrade.txt
SQL> @preupgrd.sql

Loading Pre-Upgrade Package…

***************************************************************************
Executing Pre-Upgrade Checks in ORATEST…
***************************************************************************
************************************************************
====>> ERRORS FOUND for ORATEST <<==== The following are *** ERROR LEVEL CONDITIONS *** that must be addressed prior to attempting your upgrade. Failure to do so will result in a failed upgrade. You MUST resolve the above errors prior to upgrade ************************************************************ ************************************************************ ====>> PRE-UPGRADE RESULTS for ORATEST <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /u01/11g/oracle/cfgtoollogs/oratest/preupgrade/preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade: /u01/11g/oracle/cfgtoollogs/oratest/preupgrade/preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade: /u01/11g/oracle/cfgtoollogs/oratest/preupgrade/postupgrade_fixups.sql ************************************************************ *************************************************************************** Pre-Upgrade Checks in ORATEST Completed. *************************************************************************** *************************************************************************** *************************************************************************** SQL>

Preupgrade çıktımız yukarıdaki gibi, Action Reqired kısmında yer 3 kısım son derece önemli, preupgrade.log çalıştırğımız sql ‘in çıktısının yer aldığı log dosyası, preupgrade_fixups.sql dosyası upgrade öncesinde preupgrade sql ‘inin bulduğu prerequestleri nasıl giderileceğine dair gerekli olan scriptleri bulacağımız file, postupgrade_fixups.sql dosyasında ise upgrade sonrasında çalıştırılması önerilen komutları buluyor olacağız.

Postupgrade_fixups.sql dosyasında alınması gereken bir takım manuel aksiyonlar var. Onlarıda bizim upgrade öncesinde yapıyor olmamız gerekiyor ;

[Pre-Upgrade Checks]
**********************************************************************
WARNING: –> Enterprise Manager Database Control repository found in the database

In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
– Stop EM Database Control:
$> emctl stop dbconsole

– Connect to the Database using the SYS account AS SYSDBA:

SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql

Em konsolu remove etmemiz gerekiyor. Kullanacağımız sql file target oracle home altında, burdan çalıştırıyor olmamız gerekiyor ;

[oracle@Redhat70 12c_upgrade]$ cd /u01/app/oracle/db/12.1.0.2/rdbms/admin/
[oracle@Redhat70 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 6 22:48:12 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @emremove.sql
old 69: IF (upper(‘&LOGGING’) = ‘VERBOSE’)
new 69: IF (upper(‘VERBOSE’) = ‘VERBOSE’)
PL/SQL procedure successfully completed.

İkinci olarak ;

**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: –> OLAP Catalog(AMD) exists in database

Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
***********************************************
Source oracle_home altındaki olap/admin altından aşağıdaki scripti çalıştırıyoruz.
[oracle@Redhat70 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 6 22:58:35 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @catnoamd.sql
Synonym dropped.
..
..
Type dropped.
PL/SQL procedure successfully completed.
Role dropped.
PL/SQL procedure successfully completed.
1 row deleted.
SQL>

Bir sonraki step olarak source oracle home altındaki password file ile spfile dosyasını target oracle home altındaki dbs klasörü altına taşıyoruz.
[oracle@Redhat70 dbs]$ cp /u01/11g/oracle/db/11.2.0.4/dbs/spfileoratest.ora /u01/app/oracle/db/12.1.0.2/dbs
[oracle@Redhat70 dbs]$ cp /u01/11g/oracle/db/11.2.0.4/dbs/orapworatest /u01/app/oracle/db/12.1.0.2/dbs

Bir sonraki adımda /etc/oratab dosyasını manuel olarak update ederek target oracle_home path’ i görecek şekilde düzeltiyoruz ;

oratest:/u01/app/oracle/db/12.1.0.2:N

Bundan sonraki adımda yeni oracle enviromentlarımızı set ederek devam ediyoruz;
Database’ imizi yeni oracle_home path altından upgrade modda açıyoruz ;

[oracle@Redhat70 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 6 23:31:40 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1056966928 bytes
Database Buffers 939524096 bytes
Redo Buffers 97734656 bytes
Database mounted.
Database opened.
SQL>exit

Aşağıdaki script ile upgrade ‘ e devam ediyor. Scriptteki –n parametresi ise processesin kaç parallelde çalışasacağının set edildiği kısmı ifade etmektedir. Test sunucumuz üzerinde 4 adet cpu olduğumuz için parallelliği 4 vererek scripti başlatıyoruz.

[oracle@Redhat70 admin]$ pwd
/u01/app/oracle/db/12.1.0.2/rdbms/admin
[oracle@Redhat70 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

Argument list for [catctl.pl]
SQL Process Count n = 4
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = 0
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle

Analyzing file catupgrd.sql
Log files in /u01/app/oracle/db/12.1.0.2/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_21331.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus = 4
SQL Process Count = 4

——————————————————
Phases [0-73]
Serial Phase #: 0 Files: 1 Time: 178s
Serial Phase #: 1 Files: 5 Time: 62s
….
….
Restart Phase #:61 Files: 1 Time: 0s
Serial Phase #:62 Files: 1 Time: 1604s
Restart Phase #:63 Files: 1 Time: 0s
Serial Phase #:64 Files: 1 Time: 2s
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/db/12.1.0.2/lib; export LD_LIBRARY_PATH;/u01/app/oracle/db/12.1.0.2/perl/bin/perl -I /u01/app/oracle/db/12.1.0.2/rdbms/admin -I /u01/app/oracle/db/12.1.0.2/rdbms/admin/../../sqlpatch /u01/app/oracle/db/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err
returned from sqlpatch
Time: 41s
Serial Phase #:66 Files: 1 Time: 62s
Serial Phase #:67 Files: 1 Time: 1s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Time: 18s
Grand Total Time: 3486s

Log’ un en son kısmında varsa upgrade sırasında alınan hataları görüyor olacaksınız. Bunlarıda kontrol etmekde fayda var.

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post
upgrade actions in catuppst.sql have not been automatically run.

*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

1. Evaluate the errors found in the upgrade logs
and determine the proper action.
2. Execute the post upgrade script as described in Chapter 3
of the Database Upgrade Guide.

REASON:
catuppst.sql unable to run in Database: oratest Id: 0
ERRORS FOUND: during upgrade CATCTL ERROR COUNT=3
——————————————————
Identifier CATALOG 15-05-06 11:40:14 Script = c1102000.sql
ERROR = [ORA-00600: internal error code, arguments: [kzdugt], [100], [TEST], [], [], [], [], [], [], [], [], []
]
STATEMENT = [CREATE ROLE AUDIT_VIEWER]
——————————————————
——————————————————
Identifier CATALOG 15-05-06 11:40:14 Script = c1102000.sql
ERROR = [[], [], [], [], [], []]
STATEMENT = [as above]
——————————————————
Identifier CATALOG 15-05-06 11:43:08 Script = /u01/app/oracle/db/12.1.0.2/rdbms/a
ERROR = [ORA-01917: user or role ‘AUDIT_VIEWER’ does not exist]
STATEMENT = [grant select on gv_$asm_audit_clean_events to audit_viewer]
——————————————————
LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/db/12.1.0.2/cfgtoollogs/oratest/upgrade/upg_summary.log

Grand Total Upgrade Time: [0d:0h:58m:6s]

Yine utlu121s .sql sql file yardımı ile upgrade sonuçlarını kontrol edebiliriz.

Oracle Olap API componentini remove olarak görebilirsiniz, 12c ile birlikte artık support edilmediğinden bu component kaldırılmaktadır.

Bir soraki adımda catuppst.sql çalıştırıyoruz. Hata alınması durumunda tekrar çalıştırabilirsiniz.

SQL> @catuppst.sql

Upgrade’ imizin son aşamalarına yaklaşmış olduk. İlk başta çalıştırmış olduğumuz script sonrasında, upgrade sonrasında çalışması gereken bir scriptimiz vardı onuda run edelim;

SQL> @/u01/11g/oracle/cfgtoollogs/oratest/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2015-05-06 22:18:00 Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups…

**********************************************************************
Check Tag: INVALID_OBJECTS_EXIST
Check Summary: Check for invalid objects
Fix Summary: Invalid objects are displayed and must be reviewed.
**********************************************************************
Fixup Returned Information:
WARNING: –> Database contains INVALID objects prior to upgrade

The list of invalid SYS/SYSTEM objects was written to
registry$sys_inv_objs.
The list of non-SYS/SYSTEM objects was written to
registry$nonsys_inv_objs unless there were over 5000.
Use utluiobj.sql after the upgrade to identify any new invalid
objects due to the upgrade.
**********************************************************************
**********************************************************************
Check Tag: OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: –> Older Timezone in use

Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 12.1.0.2.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************
**********************************************************************
Check Tag: NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT be upgraded
Fix Summary: This fixup does not perform any action.
**********************************************************************
Fixup Returned Information:
This fixup does not perform any action.
If you want to upgrade those other components, you must do so manually.
**********************************************************************
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
3 fixup routines generated INFORMATIONAL messages that should be reviewed.
*************** Post Upgrade Fixup Script Complete ********************
PL/SQL procedure successfully completed.

SQL>

Yukarıdaki logda çıkan recommendationalarında yapılmasında fayda var. Örneğin en kritik olanı time zone upgrade’ inin mutlaka yapılması gerekmektedir;

Şu anki timezone versiyonunu görebilmek için ;

SQL> SELECT version FROM v$timezone_file;

VERSION
———-
14

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE ‘DST_%’
4 ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE
———————————————————- ————
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0 this should be “0”
DST_UPGRADE_STATE NONE this should be “NONE”

Metalink deki 1585343.1 bulunan DBMS_DST_SCRIPTSV1.9 zip dosyayı indirip sunucu üzerine atıp ordan devam ediyoruz ; (Bu link üzerinden daha detay bilgiye erişebilisiniz.)

Bu zip file içerisinden 4 tane file bulunmaktadır. Bunlardan countstatsTSTZ.sql ve countstarTSTZ.sql opsiyonel olup çalıştırılması durumunda timezone upgrade süreninin biraz daha kısalmasına yardımcı olacaktır.

Sırayla başlayalım ;

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
20/08/2014 – SYS.AQ$_ALERT_QT_S.CREATION_TIME – 5
20/08/2014 – SYS.AQ$_ALERT_QT_S.DELETION_TIME – 5
20/08/2014 – SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME – 5
17/09/2011 – SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME – 3
17/09/2011 – SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME – 3
17/09/2011 – SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME – 3
17/09/2011 – SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME – 1
17/09/2011 – SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME – 1
17/09/2011 – SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME – 1
17/09/2011 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME – 1
21/01/2015 – SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME – 1
21/01/2015 – SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME – 1
21/01/2015 – SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME – 1
06/05/2015 – SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME – 1
06/05/2015 – SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME – 1
06/05/2015 – SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME – 1
06/05/2015 – SYS.KET$_AUTOTASK_STATUS.MW_START_TIME – 1
06/05/2015 – SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME – 1
21/01/2015 – SYS.KET$_CLIENT_CONFIG.FIELD_2 – 7
21/01/2015 – SYS.KET$_CLIENT_CONFIG.LAST_CHANGE – 7
06/05/2015 – SYS.KET$_CLIENT_TASKS.CURR_WIN_START – 3
06/05/2015 – SYS.KET$_CLIENT_TASKS.LG_DATE – 3
06/05/2015 – SYS.KET$_CLIENT_TASKS.LT_DATE – 3
06/05/2015 – SYS.OPTSTAT_HIST_CONTROL$.SPARE6 – 19
06/05/2015 – SYS.OPTSTAT_HIST_CONTROL$.SVAL2 – 19
21/01/2015 – SYS.RADM_FPTM$.TSWTZ_COL – 1
03/05/2015 – SYS.SCHEDULER$_EVENT_LOG.LOG_DATE – 9274
06/05/2015 – SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP – 11
06/05/2015 – SYS.SCHEDULER$_JOB.END_DATE – 14
06/05/2015 – SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME – 14
06/05/2015 – SYS.SCHEDULER$_JOB.LAST_END_DATE – 14
06/05/2015 – SYS.SCHEDULER$_JOB.LAST_START_DATE – 14
06/05/2015 – SYS.SCHEDULER$_JOB.NEXT_RUN_DATE – 14
06/05/2015 – SYS.SCHEDULER$_JOB.START_DATE – 14
06/05/2015 – SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE – 2882
06/05/2015 – SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE – 2882
06/05/2015 – SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE – 2882
17/09/2011 – SYS.SCHEDULER$_SCHEDULE.END_DATE – 3
17/09/2011 – SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE – 3
06/05/2015 – SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE – 9
06/05/2015 – SYS.SCHEDULER$_WINDOW.END_DATE – 9
06/05/2015 – SYS.SCHEDULER$_WINDOW.LAST_START_DATE – 9
06/05/2015 – SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME – 9
06/05/2015 – SYS.SCHEDULER$_WINDOW.NEXT_START_DATE – 9
06/05/2015 – SYS.SCHEDULER$_WINDOW.START_DATE – 9
03/05/2015 – SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE – 30
03/05/2015 – SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE – 30
03/05/2015 – SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE – 30
06/05/2015 – SYS.STATS_TARGET$.END_TIME – 1089
06/05/2015 – SYS.STATS_TARGET$.START_TIME – 1089
06/05/2015 – SYS.WRI$_ALERT_HISTORY.CREATION_TIME – 135
06/05/2015 – SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED – 135
06/05/2015 – SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME – 896641
06/05/2015 – SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 – 896641
06/05/2015 – SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME – 94442
06/05/2015 – SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 – 94442
06/05/2015 – SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME – 7349
06/05/2015 – SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 – 7349
06/05/2015 – SYS.WRI$_OPTSTAT_OPR.END_TIME – 125
06/05/2015 – SYS.WRI$_OPTSTAT_OPR.SPARE6 – 125
06/05/2015 – SYS.WRI$_OPTSTAT_OPR.START_TIME – 125
06/05/2015 – SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME – 6058
06/05/2015 – SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 – 6058
Total numrow of SYS TSTZ columns is : 2030074
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
17/12/2014 – IX.AQ$_ORDERS_QUEUETABLE_S.CREATION_TIME – 4
17/12/2014 – IX.AQ$_ORDERS_QUEUETABLE_S.DELETION_TIME – 4
17/12/2014 – IX.AQ$_ORDERS_QUEUETABLE_S.MODIFICATION_TIME – 4
17/12/2014 – IX.AQ$_STREAMS_QUEUE_TABLE_S.CREATION_TIME – 1
17/12/2014 – IX.AQ$_STREAMS_QUEUE_TABLE_S.DELETION_TIME – 1
17/12/2014 – IX.AQ$_STREAMS_QUEUE_TABLE_S.MODIFICATION_TIME – 1
Total numrow of non-SYS TSTZ columns is : 15
There are in total 30 non-SYS TSTZ columns.
Total Minutes elapsed : 0
SQL>

Sonrasında eski DBMS_SCHEDULAR loglarını silmek için;

SQL> exec dbms_scheduler.purge_log;
PL/SQL procedure successfully completed.

SYS.WRI$_OPTSTAT_HISTGRM_HISTORY ve SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY tablolarındaki datayı silmek için ;

Metalinkdeki halini aynen kopyalıyorum ;

Conn / as sysdba
— check current nr of rows in HISTHEAD / HISTGRM
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
— check the current retention of stats
— the default value is 31
select systimestamp – dbms_stats.get_stats_history_availability from dual;
— now disable stats retention
exec dbms_stats.alter_stats_history_retention(0);
— remove all stats
exec DBMS_STATS.PURGE_STATS(systimestamp);
— check result of purge
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
— AFTER the DST update you can set the retention back to the original value
exec dbms_stats.alter_stats_history_retention(31);

** logu

SQL> — check current nr of rows in HISTHEAD / HISTGRM
SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

COUNT(*)
———-
926148

SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

COUNT(*)
———-
66885

SQL> — check the current retention of stats
SQL> — the default value is 31
SQL> select systimestamp – dbms_stats.get_stats_history_availability from dual;

SYSTIMESTAMP-DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY
—————————————————————————
+000000000 00:00:27.665453000

SQL> — now disable stats retention
SQL> exec dbms_stats.alter_stats_history_retention(0);

PL/SQL procedure successfully completed.

SQL> — remove all stats
SQL> exec DBMS_STATS.PURGE_STATS(systimestamp);

PL/SQL procedure successfully completed.

SQL> — check result of purge
SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

COUNT(*)
———-
0

SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

COUNT(*)
———-
0
SQL> — AFTER the DST update you can set the retention back to the original value
SQL> exec dbms_stats.alter_stats_history_retention(31);

PL/SQL procedure successfully completed.

upg_tzv_check.sql sql’ ini basmadan önce dba_recylebini purge ediyoruz ;

SQL> purge dba_recyclebin ;
DBA Recyclebin purged.

upg_tzv_check.sql ile devam ediyoruz;

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

Scriptin logunda aşağıdaki kısımları görüyorsanız başarılı bir şekilde bitmiş demektir.

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.

Şimdi upg_tzv_apply.sql devam ediyoruz ;

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 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1056966928 bytes
Database Buffers 939524096 bytes
Redo Buffers 97734656 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 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1073744144 bytes
Database Buffers 922746880 bytes
Redo Buffers 97734656 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: “IX”.”AQ$_STREAMS_QUEUE_TABLE_S”
Number of failures: 0
Table list: “IX”.”AQ$_STREAMS_QUEUE_TABLE_L”
Number of failures: 0
Table list: “IX”.”AQ$_ORDERS_QUEUETABLE_S”
Number of failures: 0
Table list: “IX”.”AQ$_ORDERS_QUEUETABLE_L”
Number of failures: 0
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
Table list: “APEX_040200″.”WWV_FLOW_WORKSHEET_NOTIFY”
Number of failures: 0
Table list: “APEX_040200″.”WWV_FLOW_FEEDBACK_FOLLOWUP”
Number of failures: 0
Table list: “APEX_040200″.”WWV_FLOW_FEEDBACK”
Number of failures: 0
Table list: “APEX_040200”.”WWV_FLOW_DEBUG_MESSAGES2″
Number of failures: 0
Table list: “APEX_040200″.”WWV_FLOW_DEBUG_MESSAGES”
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>

Böylelikle timezone upgrade’ inide başarılı bir şekilde yapmış olduk.

Database’ imizin versiyonunu ve copanentlerin statusunu kontrol edelim ;

COMP_NAME VERSION STATUS
Oracle Application Express 4.2.5.00.08 VALID
OWB 11.2.0.3.0 VALID
Spatial 12.1.0.2.0 VALID
Oracle Multimedia 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Oracle Workspace Manager 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and Types 12.1.0.2.0 VALID
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID

===> select banner from v$version
BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
PL/SQL Release 12.1.0.2.0 – Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 – Production
NLSRTL Version 12.1.0.2.0 – Production

5 rows selected.

Reference ;
Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
http://oracle-base.com/articles/12c/upgrading-to-12c.php
http://docs.oracle.com/database/121/UPGRD/preup.htm#UPGRD12382