Manuelles Upgrade Oracle 11gR2 / 12cR1 in Oracle Enterprise Linux 6
Der Artikel beschreibt das manuelle Upgrade einer Oracle 11gR2 Single Instance Datenbank auf die Version 12cR1 mit der Out-of-Place Variante in ein neues ORACLE_HOME. Ausgangsversion ist 11.2.0.3 – Zielversion 12.1.0.2.
Ausgangslage
OS und Datenbank-Versionen
- Oracle Linux 6.5 64 Bit
- Oracle 11.2.0.3 / PSU October 2014 (19121548)
- ORACLE_HOME=/u00/app/oracle/product/11.2.0.3
- Oracle 12.1.0.2 / PSU October 2014 (19303936)
- ORACLE_HOME=/u00/app/oracle/product/12.1.0.2
- Oracle Enterprise Edition 11.2.0.3
- ORACLE_SID=SALEST
Installierte Komponenten
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> SELECT comp_name,version,status FROM dba_registry ORDER BY comp_name; COMP_NAME VERSION STATUS --------------------------------------------- ------------ ---------- JServer JAVA Virtual Machine 11.2.0.3.0 VALID Oracle Database Catalog Views 11.2.0.3.0 VALID Oracle Database Java Packages 11.2.0.3.0 VALID Oracle Database Packages and Types 11.2.0.3.0 VALID Oracle Enterprise Manager 11.2.0.3.0 VALID Oracle Expression Filter 11.2.0.3.0 VALID Oracle Rules Manager 11.2.0.3.0 VALID Oracle Text 11.2.0.3.0 VALID Oracle XDK 11.2.0.3.0 VALID Oracle XML Database 11.2.0.3.0 VALID 10 rows selected. |
Dokumente
- Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)
- Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) (Doc ID 1520299.1)
- https://blogs.oracle.com/UPGRADE/entry/welcome_to_the_oracle_database
- http://www.oracle.com/technetwork/database/upgrade/upgrading-oracle-database-wp-12c-1896123.pdf
Die neusten Features im Upgrade-Prozess
- Pre-Upgrade Skript für die Analyse der zu aktualisierenden Datenbank
- Paralleles Upgrade vom Data Dictionary inkl. Aufsetzen auf der letzten Position im Fehlerfall
- XML Datenbank wird installiert (falls nicht bereits vorhanden)
- Skript um 11g Database Control zu entfernen – wird durch 12c EM Express ersetzt
Die Schritte
- Online Backup
- Skripts preupgrd.sql und utluppkg.sql von der 12c Installation in ein separates Verzeichnis kopieren
- Preupgrd.sql in der 11gR2 Datenbank laufen lassen, Fehleranalyse und Bereingung
- 11g Listener und Datenbank stoppen
- Init.ora / Spfile / Passwort-File und Umgebungsvariablen anpassen
- 12c Listener Start
- 12c STARTUP UPGRADE
- Catalog Upgrade
- @catuppst.sql
- @utlrp.sql
- @utlu121s.sql
- @utluiobj.sql
- Time Zone aktualisieren
- Post-Steps
Upgrade ausführen
Online Backup
Login als Benutzer Oracle auf den Server und setzen der Umgebungsvariablen für die 11gR2 Datenbank SALEST mit dem Oracle Tool oraenv:
|
1 2 3 4 |
[oracle@kestenholz ~]$ . oraenv SALEST ORACLE_SID = [oracle] ? SALEST The Oracle base has been set to /u00/app/oracle |
RMAN Login
|
1 2 3 4 5 6 7 8 |
[oracle@kestenholz admin]$ rman target / nocatalog Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 1 08:44:16 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: SALEST (DBID=2318242271) using target database control file instead of recovery catalog |
Ausführen vom Backup
Es wird ein Full-Backup inklusive Controlfile ausgeführt.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
RMAN> RUN{ 2> ALLOCATE CHANNEL ch1 TYPE DISK; 3> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u00/app/oracle/admin/SALEST/backup/db_%U.bkp' TAG before_upgrade; 4> BACKUP CURRENT CONTROLFILE FORMAT '/u00/app/oracle/admin/SALEST/backup/ctl_%U.bkp'; 5> } allocated channel: ch1 channel ch1: SID=208 device type=DISK Starting backup at 01-DEC-14 channel ch1: starting compressed full datafile backup set channel ch1: specifying datafile(s) in backup set input datafile file number=00006 name=/u01/oradata/SALEST/shdata01SALEST.dbf input datafile file number=00001 name=/u01/oradata/SALEST/system01SALEST.dbf input datafile file number=00003 name=/u01/oradata/SALEST/undots01SALEST.dbf input datafile file number=00002 name=/u01/oradata/SALEST/sysaux01SALEST.dbf input datafile file number=00004 name=/u01/oradata/SALEST/users01SALEST.dbf input datafile file number=00005 name=/u01/oradata/SALEST/tools01SALEST.dbf channel ch1: starting piece 1 at 01-DEC-14 channel ch1: finished piece 1 at 01-DEC-14 piece handle=/u00/app/oracle/admin/SALEST/backup/db_07pp2djd_1_1.bkp tag=BEFORE_UPGRADE comment=NONE channel ch1: backup set complete, elapsed time: 00:00:25 channel ch1: starting compressed full datafile backup set channel ch1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ch1: starting piece 1 at 01-DEC-14 channel ch1: finished piece 1 at 01-DEC-14 piece handle=/u00/app/oracle/admin/SALEST/backup/db_08pp2dk7_1_1.bkp tag=BEFORE_UPGRADE comment=NONE channel ch1: backup set complete, elapsed time: 00:00:01 Finished backup at 01-DEC-14 Starting backup at 01-DEC-14 channel ch1: starting full datafile backup set channel ch1: specifying datafile(s) in backup set including current control file in backup set channel ch1: starting piece 1 at 01-DEC-14 channel ch1: finished piece 1 at 01-DEC-14 piece handle=/u00/app/oracle/admin/SALEST/backup/ctl_09pp2dk9_1_1.bkp tag=TAG20141201T084457 comment=NONE channel ch1: backup set complete, elapsed time: 00:00:01 Finished backup at 01-DEC-14 released channel: ch1 |
Skripts preupgrd.sql und utluppkg.sql bereitstellen
|
1 2 3 |
[oracle@kestenholz ~]$ cd /u00/app/oracle/product/12.1.0.2/rdbms/admin [oracle@kestenholz admin]$ cp preupgrd.sql /u00/app/oracle/tmp [oracle@kestenholz admin]$ cp utluppkg.sql /u00/app/oracle/tmp |
Preupgrd.sql in der 11gR2 Datenbank laufen lassen
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[oracle@kestenholz ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 26 03:41:56 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @/u00/app/oracle/tmp/preupgrd.sql |
Output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
Loading Pre-Upgrade Package... *************************************************************************** Executing Pre-Upgrade Checks in SALEST... *************************************************************************** ************************************************************ ====>> ERRORS FOUND for SALEST <<==== 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. 1) Check Tag: PURGE_RECYCLEBIN Check Summary: Check that recycle bin is empty prior to upgrade Fixup Summary: "The recycle bin will be purged." You MUST resolve the above error prior to upgrade ************************************************************ ************************************************************ ====>> PRE-UPGRADE RESULTS for SALEST <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /u00/app/oracle/cfgtoollogs/SALEST_SITE1/preupgrade/preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade: /u00/app/oracle/cfgtoollogs/SALEST_SITE1/preupgrade/preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade: /u00/app/oracle/cfgtoollogs/SALEST_SITE1/preupgrade/preupgrade_fixups.sql ************************************************************ ******* Pre-Upgrade Checks in SALEST Completed. ************ ************************************************************ |
Drei Files werden erstellt
- Logfile mit allen Findings und Empfehlungen:
- /u00/app/oracle/cfgtoollogs/SALEST_SITE1/preupgrade/preupgrade.log
- Pre-Upgrade Skript:
- /u00/app/oracle/cfgtoollogs/SALEST_SITE1/preupgrade/preupgrade_fixups.sql
- Post-Upgrade Skript:
- /u00/app/oracle/cfgtoollogs/SALEST_SITE1/preupgrade/preupgrade_fixups.sql
Die wichtigen Informationen stehen im Logfile, die beiden anderen Skripts kann man vorher und nachherlaufen lassen, zeigen aber nur den Inhalt vom Logfile an. Ich bevorzuge die Analyse vom Logfile und das manuelle durchführen von Anpassung zur Vorbereitung.
Findings
Im preupgrade.log stehen für die bestehende 11gR2 Datenbank folgende Findings für diese Installation drin – die Zusammenfassung:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[Renamed Parameters] "audit_trail" old value was "FALSE"; --> new name is "audit_trail", new value is "NONE" [Obsolete/Deprecated Parameters] --> sec_case_sensitive_logon 12.1 DESUPPORTED ERROR: --> Flashback Database is enabled and the flash recovery area is estimated not to be large enough for an upgrade. WARNING: --> Process Count may be too low WARNING: --> Enterprise Manager Database Control repository found in the database Please create stats on fixed objects two weeks after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; INFORMATION: --> Older Timezone in use |
Die Anpassungen müssen vor dem Upgrade auf der Quelldatenbank gemacht werden. SQL-Kommandos sind als Benutzer SYS / AS SYSDBA auszuführen.
Findings umsetzen
audit_trail FALSE
|
1 |
SQL> ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE; |
sec_case_sensitive_logon
|
1 |
SQL> ALTER SYSTEM RESET sec_case_sensitive_logon SID='*' SCOPE=SPFILE; |
Flashback Database zu klein
|
1 |
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 5G SCOPE=SPFILE; |
Parameter Processes
|
1 |
SQL> ALTER SYSTEM SET processes=300 SCOPE=SPFILE; |
Enterprise Manager Database Control repository
Im 12c-Verzeichnis hat es das Skript emremov.sql welches das 11g Database Control Repository in der Datenbank löscht. Zuerst wird die Konsole gestoppt:
|
1 |
[oracle@kestenholz ~]$ emctl stop dbconsole |
Und dann die Daten entfernt:
|
1 |
SQL> @/u00/app/oracle/product/12.1.0.2/rdbms/admin/emremove.sql |
RECYCLE_BIN not empty
|
1 |
SQL> PURGE dba_recyclebin; |
Data Dictionary Statistiken sammeln
|
1 2 3 |
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed. |
Older Timezone in use
Das Timezone-Problem wird im Anschluss erledigt und hat keinen Einfluss auf das Upgrade.
Neustart der Datenbank
Sicherstellen dass die neuen Parameter richtig gesetzt sind:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 612368488 bytes Database Buffers 448790528 bytes Redo Buffers 9650176 bytes Database mounted. Database opened. SQL> |
11g Listener und Datenbank stoppen
Datenbank in SQL*Plus herunterfahren
|
1 |
SQL> SHUTDOWN IMMEDIATE |
Listener als OS-Benutzer oracle stoppen
|
1 2 3 4 5 6 7 8 |
[oracle@kestenholz ~]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-DEC-2014 09:39:12 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully |
Init.ora / Spfile / Passwort-File und Umgebungsvariablen anpassen
Das Server-Parameterfile und das Passwortfile müssen in das neue ORACLE_HOME/dbs kopiert werden. Hier ist das Server-Parameterfile im Administrationsverezichnis der Datenbank gespeichert und muss nur neu verlinkt werden.
|
1 2 3 |
[oracle@kestenholz ~]$ cd /u00/app/oracle/product/12.1.0.2/dbs/ [oracle@kestenholz dbs]$ cp /u00/app/oracle/product/11.2.0.3/dbs/orapwSALEST . [oracle@kestenholz dbs]$ ln -s /u00/app/oracle/admin/SALEST/pfile/spfileSALEST.ora |
Das oratab File ist mit dem neuen ORACLE_HOME anzupassen:
|
1 2 |
[oracle@kestenholz ~]$ vi /etc/oratab SALEST:/u00/app/oracle/product/12.1.0.2:Y |
Danach können die neuen Umgebungsvariablen gesetzt und gestestet werden:
|
1 2 3 |
[oracle@kestenholz ~]$ . oraenv SALEST ORACLE_SID = [SALEST] ? The Oracle base remains unchanged with value /u00/app/oracle |
|
1 2 3 4 |
[oracle@kestenholz ~]$ echo $ORACLE_HOME /u00/app/oracle/product/12.1.0.2 [oracle@kestenholz ~]$ echo $ORACLE_SID SALEST |
12c Listener Start
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
[oracle@kestenholz dbs]$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2014 09:40:41 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u00/app/oracle/product/12.1.0.2/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production Log messages written to /u00/app/oracle/diag/tnslsnr/kestenholz/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kestenholz)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 01-DEC-2014 09:40:41 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u00/app/oracle/diag/tnslsnr/kestenholz/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kestenholz)(PORT=1521))) The listener supports no services The command completed successfully |
12c STARTUP UPGRADE
Nachdem die neuen Umgebungsvariablen gesetzt sind, kann die Datenbank im UPGRADE Modus gestartet werden.
|
1 2 3 4 5 6 7 8 9 10 |
[oracle@kestenholz dbs]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 1 09:40:56 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> STARTUP UPGRADE SQL> exit |
Catalog Upgrade
Data Dictionary updaten
Ausführen vom neuen catctl.pl Skript mit Angabe vom Parallelisierungsgrad. Wird kein Parameter -n mitgegeben so wird der Parallelisierungsgrad 4 verwendet, maximaler Wert ist 8.
|
1 2 3 4 5 6 |
[oracle@kestenholz ~]$ cd $ORACLE_HOME/rdbms/admin [oracle@kestenholz admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 catupgrd.sql ... Analyzing file catupgrd.sql Log files in /u00/app/oracle/product/12.1.0.2/rdbms/admin ... |
Auszug der Ausführung
|
1 2 3 4 5 6 7 8 9 10 |
Phases [0-73] Serial Phase #: 0 Files: 1 Time: 44s Serial Phase #: 1 Files: 5 Time: 25s Restart Phase #: 2 Files: 1 Time: 1s Parallel Phase #: 3 Files: 18 Time: 5s Restart Phase #: 4 Files: 1 Time: 0s Serial Phase #: 5 Files: 5 Time: 11s Serial Phase #: 6 Files: 1 Time: 8s Serial Phase #: 7 Files: 4 Time: 7s ... |
Fehler bei Schritt 66 – Died at catcon.pm line 6149
Es ist möglich dass das Skript bei Schritt 66 abbricht mit der folgenden Fehlermeldung:
|
1 2 3 |
Serial Phase #:66 Files: 1 A process terminated prior to completion. Review the catupgrd*.log files to identify the failure Died at catcon.pm line 6149. |
Die Analyse der Logfiles im Verzeichnis $ORACLE_HOME/rdbms /admin zeigt:
|
1 2 3 4 5 6 7 |
catrequtlmg: Gathering Table Stats OBJ$MIG declare * ERROR at line 1: ORA-20000: Unable to gather statistics concurrently: Resource Manager is not enabled. ORA-06512: at "SYS.DBMS_STATS", line 34634 ORA-06512: at line 152 |
Anscheinend wird in 12c die Statistik-Sammelmethode auf CONCURRENT geändert und der Statistikjob kann damit nicht umgehen. Mike Dietrich beschreibt im Oracle Upgrade Blog (https://blogs.oracle.com/UPGRADE/entry/ora_20000_unable_to_gather) den Fall und den Workaround dazu.
Login als SYS / AS SYSDBA und den Modus deaktivieren
|
1 |
SQL> EXEC dbms_stats.set_global_prefs('CONCURRENT', 'FALSE'); |
Neustart im UPGRADE Modus
|
1 2 3 |
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP UPGRADE SQL> exit |
Aufsetzen bei Step 66
Danach kann das Data Dictionary Upgrade beim Schritt wo abgebrochen wurde aufsetzen, dazu kann man den Parameter -p mitgeben.
|
1 |
[oracle@kestenholz admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -p 66 catupgrd.sql |
Upgrade beendet
Für die restlichen Schritte werden nur noch ein paar Sekunden benötigt. Die Datenbank wurde durch das Upgrade-Skript runtergefahren.
|
1 2 3 4 |
Upgrade Summary Report Located in: /u00/app/oracle/product/12.1.0.2/cfgtoollogs/SALEST_SITE1/upgrade/upg_summary.log Grand Total Upgrade Time: [0d:0h:0m:32s] |
Danach muss die Datenbank neu gestartet werden.
|
1 |
SQL> STARTUP |
Kontrolle der Komponenten
Die Version wurde aktualisiert, der UPGRADE Status der einzelnen Komponenten wird nach dem Kompilieren geändert. Expression Filter und Rules Manager wurden entfernt (Obsolescence Notice: Rules Manager and Expression Filter Features of Oracle Database (Doc ID 1244535.1))
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> SELECT comp_name,version,status FROM dba_registry ORDER BY comp_name; COMP_NAME VERSION STATUS --------------------------------------------- ------------ ---------- JServer JAVA Virtual Machine 12.1.0.2.0 VALID Oracle Database Catalog Views 12.1.0.2.0 UPGRADED Oracle Database Java Packages 12.1.0.2.0 VALID Oracle Database Packages and Types 12.1.0.2.0 UPGRADED Oracle Text 12.1.0.2.0 VALID Oracle XDK 12.1.0.2.0 VALID Oracle XML Database 12.1.0.2.0 VALID 7 rows selected. |
@catuppst.sql
Ausführen als SYS / AS SYSDBA:
|
1 2 3 4 5 6 7 8 |
SQL> @?/rdbms/admin/catuppst.sql ... TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP POSTUP_END 2014-12-01 09:53:17 Session altered. |
@utlrp.sql
Ausführen als SYS / AS SYSDBA:
|
1 |
SQL> @?/rdbms/admin/utlrp.sql |
Kontrolle der Komponenten
Die Version wurde aktualisiert, sämtlich Komponenten haben den Status VALID:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> SELECT comp_name,version,status FROM dba_registry ORDER BY comp_name; COMP_NAME VERSION STATUS --------------------------------------------- ------------ ---------- JServer JAVA Virtual Machine 12.1.0.2.0 VALID Oracle Database Catalog Views 12.1.0.2.0 VALID Oracle Database Java Packages 12.1.0.2.0 VALID Oracle Database Packages and Types 12.1.0.2.0 VALID Oracle Text 12.1.0.2.0 VALID Oracle XDK 12.1.0.2.0 VALID Oracle XML Database 12.1.0.2.0 VALID 7 rows selected. |
@utlu121s.sql
Das Script zeigt Upgrade-informationen und Laufzeiten an.
|
1 |
SQL> @?/rdbms/admin/utlu121s.sql |
Output
|
1 2 3 4 5 6 7 8 9 10 |
Oracle Server VALID 12.1.0.2.0 00:05:31 JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:00:50 Oracle XDK VALID 12.1.0.2.0 00:00:21 Oracle Text VALID 12.1.0.2.0 00:00:27 Oracle XML Database VALID 12.1.0.2.0 00:00:52 Oracle Database Java Packages VALID 12.1.0.2.0 00:00:06 Final Actions 00:00:18 Post Upgrade 00:00:01 Total Upgrade Time: 00:08:39 |
@utluiobj.sql
Prüfung auf invalide Objekte.
|
1 |
SQL> @?/rdbms/admin/utluiobj.sql |
Output
|
1 2 3 4 5 6 7 8 9 10 |
Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 12-01-2014 09:58:08 . This tool lists post-upgrade invalid objects that were not invalid prior to upgrade (it ignores pre-existing pre-upgrade invalid objects). . Owner Object Name Object Type . PL/SQL procedure successfully completed. |
Time Zone aktualisieren
Die My Oracle Support Note Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1) beschreibt die Aktualisierung und stellt Skripts für die Prüfung und die Aktualisierung bereit.
Version prüfen
|
1 2 3 4 5 6 7 8 |
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES 2 WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE ------------------------------ ---------------------------------------- DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE |
Gemäss der My Oracle Support Note Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1) muss die Version 18 verwendet werden.
Time Zone Script
Das Script aus der My Oracle Support Note wird via FTP auf den Server transferiert und entpackt:
|
1 2 3 4 5 6 |
oracle@kestenholz:/u00/app/oracle/tmp/ [SALEST] unzip DBMS_DST_scriptsV1.9.zip Archive: DBMS_DST_scriptsV1.9.zip creating: DBMS_DST_scriptsV1.9/ inflating: DBMS_DST_scriptsV1.9/countTSTZdata.sql inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql |
- upg_tzv_check.sql – prüft die Version
- upg_tzv_apply.sql – aktualisiert die Version
Prüfen der Version als SYSDBA
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> @/u00/app/oracle/tmp/DBMS_DST_scriptsV1.9/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. |
Version aktualisieren
Die Datenbank wird automatisch neu gestartet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
SQL> @/u00/app/oracle/tmp/DBMS_DST_scriptsV1.9/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 1073741824 bytes Fixed Size 2932632 bytes Variable Size 499122280 bytes Database Buffers 562036736 bytes Redo Buffers 9650176 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 1073741824 bytes Fixed Size 2932632 bytes Variable Size 499122280 bytes Database Buffers 562036736 bytes Redo Buffers 9650176 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. |
Neue Version prüfen
|
1 2 3 4 5 6 7 8 |
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES 2 WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE ------------------------------ ---------------------------------------- DST_PRIMARY_TT_VERSION 18 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE |
Post-Steps
Parameter compatible
Der Parameter muss auf die Version 12.1.0 angepasst werden – ein Neustart wird benötigt:
|
1 2 3 |
SQL> ALTER SYSTEM SET compatible='12.1.0' SCOPE=SPFILE; SQL> SHUTDOWN IMMEDIATE SQL> STARTUP |
Sammeln der neuen Data Dictionary Statistiken
|
1 |
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; |
EM Express konfigurieren
Setzen des https-Ports:
|
1 |
SQL> EXEC dbms_xdb_config.sethttpsport(5500); |
Verifikation am Listener ob EM Express mit Port 5500 registriert ist:
|
1 |
[oracle@kestenholz tmp]$ lsnrctl status | grep -i 5500 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=kestenholz)(PORT=5500))(Security=(my_wallet_directory=/u00/app/oracle/admin/SALEST_SITE1/xdb_wallet))(Presentation=HTTP)(Session=RAW)) |
Weitere Information rund um die Konfiguration von EM Express: https://docs.oracle.com/database/121/ADMQS/em_manage.htm#ADMQS003
Test EM Express mit der URL https://kestenholz:5500/em:
