Upgrade Oracle Database Manually from 11.2.0.4 to 12.2.0.1
Contents
___________________________________________________________________________________________________
1. Environment
2. Run preupgrade script
3. View Preupgrade log
4. Remove the EM repository
5. Remove OLAP Catalog
6. Update INITIALIZATION PARAMETERS
7. Gather DICTIONARY STATS
8. Purge Recyclebin
9. Refresh MVs
10. Run preupgrade_fixups.sql
11. Verify archive log dest size
12. Stop LISTENER
13. Create Flashback Guaranteed Restore Point
14. Shutdown Database
15. Copy init and password files from 11g to 12c dbs home
16. Startup DB in Upgrade mode
17. Run catctl.pl with catupgrd.sql
18. Run postupgrade_fixups.sql
19. Update Timezone
20. Re-Run postupgrade_fixups.sql
21. Run utlu122s.sql
22. Run catuppst.sql
23. Run utlrp.sql
24. Drop Restore point
25. Set COMPATIBALE parameter value to 12.2.0
26. Restart the database
27. Start Listener from 12c home
28. Verify
29. Edit oratab
___________________________________________________________________________________________________
Hostname : RAC1.RAJASEKHAR.COM Database Name : DELL DB VERSION : 11.2.0.4.0 DB Home Path : /u01/app/oracle/product/11.2.0.4 Datafile Location : /u02/oradata/DELL Target DB VERSION : 12.2.0.1 Target DB Path : /u01/app/oracle/product/12.2.0.1 Upgrade Method : Manual
/u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar TERMINAL TEXT --- OR --- /u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade [oracle@rac1 ~]$ mkdir -p /home/oracle/preupgrade [oracle@rac1 ~]$ [oracle@rac1 preupgrade]$ /u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade Preupgrade generated files: /home/oracle/preupgrade/preupgrade.log /home/oracle/preupgrade/preupgrade_fixups.sql /home/oracle/preupgrade/postupgrade_fixups.sql [oracle@rac1 preupgrade]$
[oracle@rac1 preupgrade]$ cat /home/oracle/preupgrade/preupgrade.log Report generated by Oracle Database Pre-Upgrade Information Tool Version 12.2.0.1.0 Upgrade-To version: 12.2.0.1.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: DELL Container Name: Not Applicable in Pre-12.1 database Container ID: Not Applicable in Pre-12.1 database Version: 11.2.0.4.0 Compatible: 11.2.0.4.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 14 Database log mode: ARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID JServer JAVA Virtual Machine [to be upgraded] VALID Oracle XDK for Java [to be upgraded] VALID Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Enterprise Manager Repository [to be upgraded] VALID Oracle Text [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID Oracle Java Packages [to be upgraded] VALID Oracle Multimedia [to be upgraded] VALID Oracle Spatial [to be upgraded] VALID Expression Filter [to be upgraded] VALID Rule Manager [to be upgraded] VALID Oracle Application Express [to be upgraded] VALID Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run /preupgrade_fixups.sql to complete all of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ + Adjust TABLESPACE SIZES as needed. Auto 12.2.0.1.0 Tablespace Size Extend Min Size Action ---------- ---------- -------- ---------- ------ EXAMPLE 346 MB DISABLED 309 MB None SYSAUX 500 MB ENABLED 1411 MB None SYSTEM 750 MB ENABLED 1260 MB None TEMP 20 MB ENABLED 150 MB None UNDOTBS1 65 MB ENABLED 400 MB None Note that 12.2.0.1.0 minimum sizes are estimates. If you plan to upgrade multiple pluggable databases concurrently, then you must ensure that the UNDO tablespace size is equal to at least the number of pluggable databases that you upgrade concurrently, multiplied by that minimum. Failing to allocate sufficient space can cause the upgrade to fail. + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums. Parameter 12.2.0.1.0 minimum --------- ------------------ processes 300 RECOMMENDED ACTIONS =================== + Remove the EM repository. - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME. Step 1: If database control is configured, stop EM Database Control, using the following command $> emctl stop dbconsole Step 2: Connect to the database using the SYS account AS SYSDBA SET ECHO ON; SET SERVEROUTPUT ON; @emremove.sql Without the set echo and serveroutput commands, you will not be able to follow the progress of the script. The database has an Enterprise Manager Database Control repository. Starting with Oracle Database 12c, the local Enterprise Manager Database Control does not exist anymore. The repository will be removed from your database during the upgrade. This step can be manually performed before the upgrade to reduce downtime. + Remove OLAP Catalog by running the 11.2.0.4.0 SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script. The OLAP Catalog component, AMD, exists in the database. Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is desupported and will be automatically marked as OPTION OFF during the database upgrade if present. Oracle recommends removing OLAP Catalog (OLAP AMD) before database upgrade. + (AUTOFIXUP) Gather stale data dictionary statistics prior to database upgrade in off-peak time using: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Dictionary statistics do not exist or are stale (not up-to-date). Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade. For information on managing optimizer statistics, refer to the 11.2.0.4 Oracle Database Performance Tuning Guide. + Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the trigger or drop and re-create the trigger with a user that was granted directly with such. You can list those triggers using "SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE=''DATABASE'' AND OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE=''ADMINISTER DATABASE TRIGGER'')" There is one or more database triggers whose owner does not have the right privilege on the database. The creation of database triggers must be done by users granted with ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted directly. + Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes empty before doing upgrade, unless you have strong business reasons not to do so. You can use dbms_mview.refresh() to refresh the MVs except those stale ones to be kept due to business need. If there are any stale MVs depending on changes in sys.sumdelta$, do not truncate it, because doing so will cause wrong results after refresh. There is one or more non-fresh MV in the database or sumdelta$ is not empty. Oracle recommends that all materialized views (MV's) are refreshed before upgrading the database because this will clear the MV logs and the sumdelta$ table, and make the UPGRADE process faster. If you choose to not refresh some MVs, the change data for those MV's will be carried through the UPGRADE process. After UPGRADE, you can refresh the MV's and MV incremental refresh should work in normal cases. INFORMATION ONLY ================ + Consider upgrading APEX manually, before the database upgrade. The database contains APEX version 3.2.1.00.12 and will need to be upgraded to at least version 5.0.4.00.12. To reduce database upgrade time, you can upgrade APEX manually before the database upgrade. Refer to My Oracle Support Note 1088970.1 for information on APEX installation upgrades. + Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least 5315 MB of archived logs. Check alert log during the upgrade that there is no write error to the destination due to lack of disk space. Execute 'archive log list' and query v$archive_dest for more LOG_ARCHIVE_DEST_ destinations to check. Archiving cannot proceed if the archive log destination is full during upgrade. Archive Log Destination: Parameter : LOG_ARCHIVE_DEST_1 Destination : /u02/arch/DELL/ The database has archiving enabled and LOG_ARCHIVE_DEST_ set. The upgrade process will need free disk space in the archive log destination(s) to generate archived logs to. ============= AFTER UPGRADE ============= Run /postupgrade_fixups.sql to complete all of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== + If you use the -T option for the database upgrade, then run $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete, to VALIDATE and UPGRADE any user tables affected by changes to Oracle-Maintained types. There are user tables dependent on Oracle-Maintained object types. If the -T option is used to set user tablespaces to READ ONLY during the upgrade, user tables in those tablespaces, that are dependent on Oracle-Maintained types, will not be automatically upgraded. If a type is evolved during the upgrade, any dependent tables need to be re-validated and upgraded to the latest type version AFTER the database upgrade completes. + Upgrade the database time zone version using the DBMS_DST package. The database is using timezone datafile version 14 and the target 12.2.0.1.0 database ships with timezone datafile version 26. Oracle recommends using the most recent timezone data. For further information, refer to My Oracle Support Note 1585343.1. + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. + Gather statistics on fixed objects two weeks after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; This recommendation is given for all preupgrade runs. Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade. INFORMATION ONLY ================ + Check the Oracle documentation for the identified components for their specific upgrade procedure. The database upgrade script will not upgrade the following Oracle components: OLAP Catalog,OWB The Oracle database upgrade script upgrades most, but not all Oracle Database components that may be installed. Some components that are not upgraded may have their own upgrade scripts, or they may be deprecated or obsolete. [oracle@rac1 preupgrade]$
[oracle@rac1 preupgrade]$ emctl stop dbconsole SQL> select name,open_mode,version from v$database,v$instance; NAME OPEN_MODE VERSION --------- -------------------- ----------------- DELL READ WRITE 11.2.0.4.0 SQL> !ls -ltr /u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql -rw-r--r--. 1 oracle dba 20740 Aug 8 2016 /u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql SQL> @/u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql old 70: IF (upper('&LOGGING') = 'VERBOSE') new 70: IF (upper('VERBOSE') = 'VERBOSE') PL/SQL procedure successfully completed. SQL>
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> !ls -lrt /u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql
-rw-r--r--. 1 oracle dba 11916 Apr 13 2013 /u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql
SQL> @/u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql
6. Update INITIALIZATION PARAMETERS
SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
System altered.
SQL>
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL>
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
SQL>
SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SQL>
SQL> SET ECHO ON; SQL> SET SERVEROUTPUT ON; SQL> @/home/oracle/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 1 Generated on: 2018-09-15 20:14:51 For Source Database: DELL Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ em_present Passed None amd_exists Passed None dictionary_stats Passed None trgowner_no_admndbtrg Passed None mv_refresh Passed None apex_upgrade_msg Failed Manual fixup recommended. min_archive_dest_size Failed Manual fixup recommended. PL/SQL procedure successfully completed. SQL>
11. Verify archive log dest size
*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations, including standby side SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/arch/DELL/ Oldest online log sequence 11 Next log sequence to archive 13 Current log sequence 13 SQL> !df -h /u02/arch/DELL/ Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup-lv_home 43G 24G 18G 57% /u02 SQL>
[oracle@rac1 ~]$ ps -ef | grep tns [oracle@rac1 ~]$ lsnrctl stop LISTENER
13. Create Flashback Guaranteed Restore Point
*** NO need to enable Flashback Database from 11.2.0.1 onward *** Database MUST be in Archive Log mode *** MUST NOT change the compatible parameter to higher version SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO <------ SQL> SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE --------- -------------------- ------------ DELL READ WRITE ARCHIVELOG <------ SQL> SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.4.0 <---- SQL> SQL> alter system set db_recovery_file_dest='/u02/fast_recovery_area/DELL'; System altered. SQL> alter system set db_recovery_file_dest_size=10G; System altered. SQL> SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u02/fast_recovery_area/DELL db_recovery_file_dest_size big integer 10G recovery_parallelism integer 0 SQL> [oracle@rac1 ~]$ df -h /u02/fast_recovery_area/DELL Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup-lv_home 43G 24G 18G 57% /u02 [oracle@rac1 ~]$ SQL> select * from V$restore_point; no rows selected SQL> SQL> create restore point pre_upgrade guarantee flashback database; Restore point created. SQL> SQL> col name for a20 SQL> col GUARANTEE_FLASHBACK_DATABASE for a10 SQL> col TIME for a60 SQL> set lines 190 SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- --------------------------------- PRE_UPGRADE YES 15-SEP-18 09.53.59.000000000 PM SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
15. Copy init and password files from 11g to 12c dbs home
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0.4/dbs [oracle@rac1 dbs]$ ls -ltr *DELL* -rw-r-----. 1 oracle dba 24 Aug 14 19:48 lkDELL -rw-r-----. 1 oracle dba 1536 Aug 14 19:50 orapwDELL -rw-r-----. 1 oracle dba 2560 Sep 15 20:35 spfileDELL.ora -rw-rw----. 1 oracle dba 1544 Sep 15 21:03 hc_DELL.dat [oracle@rac1 dbs]$ cp orapwDELL spfileDELL.ora /u01/app/oracle/product/12.2.0.1/dbs [oracle@rac1 dbs]$ [oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/12.2.0.1/dbs/*DELL* -rw-r-----. 1 oracle dba 2560 Sep 15 21:10 /u01/app/oracle/product/12.2.0.1/dbs/spfileDELL.ora -rw-r-----. 1 oracle dba 1536 Sep 15 21:10 /u01/app/oracle/product/12.2.0.1/dbs/orapwDELL [oracle@rac1 dbs]$
16. Startup DB in Upgrade mode
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1 [oracle@rac1 ~]$ export ORACLE_SID=DELL [oracle@rac1 ~]$ PATH=/u01/app/oracle/product/12.2.0.1/bin:$PATH; export PATH [oracle@rac1 ~]$ which sqlplus /u01/app/oracle/product/12.2.0.1/bin/sqlplus [oracle@rac1 ~]$ [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 15 22:05:13 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade; ORACLE instance started. Total System Global Area 1660944384 bytes Fixed Size 8621376 bytes Variable Size 1056965312 bytes Database Buffers 587202560 bytes Redo Buffers 8155136 bytes Database mounted. Database opened. SQL> SQL> select instance_name,version,status from v$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ------------ DELL 12.2.0.1.0 OPEN MIGRATE <---- SQL>
17. Run catctl.pl with catupgrd.sql
[oracle@rac1 ~]$ mkdir -p /home/oracle/whileupgrade [oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0.1/rdbms/admin [oracle@rac1 admin]$ nohup /u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql & [1] 16004 [oracle@rac1 admin]$ nohup: ignoring input and appending output to `nohup.out' [oracle@rac1 admin]$ disown [oracle@rac1 admin]$ ps -ef | grep -i catctl.pl oracle 16004 6189 1 22:22 pts/0 00:00:01 /u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql oracle 16324 6189 0 22:23 pts/0 00:00:00 grep -i catctl.pl [oracle@rac1 admin]$ [oracle@rac1 admin]$ more nohup.out Argument list for [catctl.pl] Run in c = 0 Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = 0 Child Process I = 0 Log Dir l = /home/oracle/whileupgrade Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 4 SQL PDB Process Count N = 0 Open Mode Normal o = 0 Start Phase p = 0 End Phase P = 0 Reverse Order r = 0 AutoUpgrade Resume R = 0 Script s = 0 Serial Run S = 0 RO User Tablespaces T = 0 Display Phases y = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 catctl.pl VERSION: [12.2.0.1.0] STATUS: [production] BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170107] .. .. .. .. LOG FILES: (/home/oracle/whileupgrade/catupgrd*.log) Upgrade Summary Report Located in: /home/oracle/whileupgrade/upg_summary.log <---- Grand Total Upgrade Time: [0d:1h:24m:24s] [oracle@rac1 admin]$ SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;SQL> SQL> SQL> SQL> SQL> COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- ----------- CATALOG Oracle Database Catalog Views 12.2.0.1.0 UPGRADED CATPROC Oracle Database Packages and Types 12.2.0.1.0 UPGRADED JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED XML Oracle XDK 12.2.0.1.0 UPGRADED CATJAVA Oracle Database Java Packages 12.2.0.1.0 UPGRADED APS OLAP Analytic Workspace 12.2.0.1.0 UPGRADED OWM Oracle Workspace Manager 12.2.0.1.0 UPGRADED CONTEXT Oracle Text 12.2.0.1.0 UPGRADED XDB Oracle XML Database 12.2.0.1.0 UPGRADED ORDIM Oracle Multimedia 12.2.0.1.0 UPGRADED SDO Spatial 12.2.0.1.0 UPGRADED XOQ Oracle OLAP API 12.2.0.1.0 UPGRADED APEX Oracle Application Express 5.0.4.00.12 UPGRADED 13 rows selected. SQL>
18. Run postupgrade_fixups.sql
[oracle@rac1 preupgrade]$ pwd /home/oracle/preupgrade [oracle@rac1 preupgrade]$ which sqlplus /u01/app/oracle/product/12.2.0.1/bin/sqlplus [oracle@rac1 preupgrade]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 16 00:32:19 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> !ls -ltr postupgrade_fixups.sql -rw-r--r--. 1 oracle dba 8129 Sep 15 20:14 postupgrade_fixups.sql SQL> @postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 1 Generated on: 2018-09-15 20:14:52 For Source Database: DELL Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ depend_usr_tables Failed Manual fixup recommended. old_time_zones_exist Failed Manual fixup recommended. post_dictionary Passed None fixed_objects Passed None upg_by_std_upgrd Passed None PL/SQL procedure successfully completed. Session altered. SQL> SQL>
Download DBMS_DST_scriptsV1.9.zip from Oracle Support 1585343.1 Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1) [oracle@rac1 DBMS_DST_scriptsV1.9]$ pwd /home/oracle/TZ/DBMS_DST_scriptsV1.9 [oracle@rac1 DBMS_DST_scriptsV1.9]$ ls -ltr total 68 -rw-r--r--. 1 oracle dba 31010 Aug 22 2014 upg_tzv_check.sql -rw-r--r--. 1 oracle dba 19502 Aug 22 2014 upg_tzv_apply.sql -rw-r--r--. 1 oracle dba 6294 Jan 8 2015 countstarTSTZ.sql -rw-r--r--. 1 oracle dba 7213 Mar 17 18:30 countstatsTSTZ.sql [oracle@rac1 DBMS_DST_scriptsV1.9]$ [oracle@rac1 DBMS_DST_scriptsV1.9]$ which sqlplus /u01/app/oracle/product/12.2.0.1/bin/sqlplus [oracle@rac1 DBMS_DST_scriptsV1.9]$ [oracle@rac1 DBMS_DST_scriptsV1.9]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 16 00:54:44 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 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.2.0.1 . 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 DSTv26 . 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> SQL> SELECT version FROM v$timezone_file; VERSION ---------- 14 <---- 1 row selected. 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 DSTv26 . 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 1660944384 bytes Fixed Size 8621376 bytes Variable Size 1157628608 bytes Database Buffers 486539264 bytes Redo Buffers 8155136 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 1660944384 bytes Fixed Size 8621376 bytes Variable Size 1056965312 bytes Database Buffers 587202560 bytes Redo Buffers 8155136 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$_ORDERS_QUEUETABLE_L" Number of failures: 0 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES2" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_FEEDBACK" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_FEEDBACK_FOLLOWUP" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_WORKSHEET_NOTIFY" 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 DSTv26 . 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> SQL> SELECT version FROM v$timezone_file; VERSION ---------- 26 <---- 1 row selected. SQL>
20. Re-Run postupgrade_fixups.sql
SQL> @/home/oracle/preupgrade/postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 1 Generated on: 2018-09-15 20:14:52 For Source Database: DELL Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ depend_usr_tables Failed Manual fixup recommended. <-- Ignore this. old_time_zones_exist Passed None post_dictionary Passed None fixed_objects Passed None upg_by_std_upgrd Passed None PL/SQL procedure successfully completed. Session altered. SQL> 12.2 DB Post Upgrade:postupgrade_fixups.sql shows "depend_usr_tables Failed Manual fixup recommended" (Doc ID 2266026.1) Solution: 1) If Database Upgraded without using -T option,then FAILED status to be ignored. or 2) If Database Upgraded with using -T option . Then execute $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete. And then ignore FAILED status . [oracle@rac1 admin]$ more nohup.out Argument list for [catctl.pl] Run in c = 0 Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = 0 Child Process I = 0 Log Dir l = /home/oracle/whileupgrade Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 4 SQL PDB Process Count N = 0 Open Mode Normal o = 0 Start Phase p = 0 End Phase P = 0 Reverse Order r = 0 AutoUpgrade Resume R = 0 Script s = 0 Serial Run S = 0 RO User Tablespaces T = 0 <-------- Display Phases y = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0
SQL> @/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlu122s.sql
Oracle Database 12.2 Post-Upgrade Status Tool 09-16-2018 01:10:10
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
..
..
..
Oracle Server UPGRADED 12.2.0.1.0 00:19:59
JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:10:28
Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:01:36
OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:49
Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:23
Oracle XDK UPGRADED 12.2.0.1.0 00:01:16
Oracle Text UPGRADED 12.2.0.1.0 00:01:37
Oracle XML Database UPGRADED 12.2.0.1.0 00:04:08
Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:23
Oracle Multimedia UPGRADED 12.2.0.1.0 00:03:25
Spatial UPGRADED 12.2.0.1.0 00:09:48
Oracle Application Express UPGRADED 5.0.4.00.12 00:21:35
Final Actions 00:03:25
Post Upgrade 00:02:26
Total Upgrade Time: 01:21:57
Database time zone version is 26. It meets current release needs.
Summary Report File = /home/oracle/whileupgrade/upg_summary.log
01:10:10 SQL>
01:10:10 SQL>
SQL> @/u01/app/oracle/product/12.2.0.1/rdbms/admin/catuppst.sql
Session altered.
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2018-09-16 01:12:55
DBUA_TIMESTAMP DBRESTART FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP DBRESTART NONE 2018-09-16 01:12:55
TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2018-09-16 01:12:55
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2018-09-16 01:12:55
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP POSTUP_BGN NONE 2018-09-16 01:12:55
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_BGN NONE 2018-09-16 01:12:55
PL/SQL procedure successfully completed.
catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_END FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_END NONE 2018-09-16 01:12:55
catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
PL/SQL procedure successfully completed.
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2018-09-16 01:12:59
DBUA_TIMESTAMP POSTUP_END FINISHED 2018-09-16 01:12:59
DBUA_TIMESTAMP POSTUP_END NONE 2018-09-16 01:12:59
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2018-09-16 01:12:59
DBUA_TIMESTAMP CATUPPST FINISHED 2018-09-16 01:12:59
DBUA_TIMESTAMP CATUPPST NONE 2018-09-16 01:12:59
Session altered.
SQL>
Run in background cd /u01/app/oracle/product/12.2.0.1/rdbms/admin nohup sqlplus "/ as sysdba" @utlrp.sql > /home/oracle/utlrp.out 2>&1 & ---- OR ---- SQL> @/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp.sql TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2018-09-16 01:14:35 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2018-09-16 01:21:18 DOC> The following query reports the number of invalid objects. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of exceptions caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC> Note: Typical compilation errors (due to coding errors) are not DOC> logged into this table: they go into DBA_ERRORS instead. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. ...Starting validation 01:21:36 ...Database user "SYS", database schema "APEX_050000", user# "128" 01:21:36 ...grant execute on "SYS"."DBMS_CRYPTO_INTERNAL" to APEX_050000 01:21:38 ...272 packages ...265 package bodies ...465 tables ...8 functions ...16 procedures ...4 sequences ...497 triggers ...1582 indexes ...255 views ...0 libraries ...14 types ...5 type bodies ...0 operators ...0 index types ...Begin key object existence check 01:21:43 ...Completed key object existence check 01:21:43 ...Setting DBMS Registry 01:21:43 ...Setting DBMS Registry Complete 01:21:43 ...Exiting validate 01:21:43 PL/SQL procedure successfully completed. SQL> SQL> select count(1) from dba_objects where status='INVALID'; COUNT(1) ---------- 0 <---- SQL>
SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; SQL> SQL> SQL> SQL> NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ PRE_UPGRADE YES 15-SEP-18 09.53.59.000000000 PM SQL> SQL> drop restore point PRE_UPGRADE; Restore point dropped. SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; no rows selected <----- SQL>
25. Set COMPATIBALE parameter value to 12.2.0
Warning: If the value of COMPATIBLE parameter is changed to 12.2.0.1 then if for some reasons database needs to be downgraded to 11.2.0.4 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.
If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.
SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.4.0 noncdb_compatible boolean FALSE SQL> SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE; System altered. SQL>
SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup; ORACLE instance started. Total System Global Area 1660944384 bytes Fixed Size 8621376 bytes Variable Size 1090519744 bytes Database Buffers 553648128 bytes Redo Buffers 8155136 bytes Database mounted. Database opened. SQL>
27. Start Listener from 12c home
[oracle@rac1 admin]$ lsnrctl start LISTENER [oracle@rac1 admin]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-SEP-2018 01:43:41 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 16-SEP-2018 01:42:15 Uptime 0 days 0 hr. 1 min. 25 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "DELL" has 1 instance(s). Instance "DELL", status READY, has 1 handler(s) for this service... Service "DELLXDB" has 1 instance(s). Instance "DELL", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 admin]$
SQL> select name,open_mode,version from v$database,v$instance; NAME OPEN_MODE VERSION -------------------- -------------------- ----------------- DELL READ WRITE 12.2.0.1.0 SQL> SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;SQL> SQL> SQL> SQL> SQL> COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- ----------- CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID XML Oracle XDK 12.2.0.1.0 VALID CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID APS OLAP Analytic Workspace 12.2.0.1.0 VALID OWM Oracle Workspace Manager 12.2.0.1.0 VALID CONTEXT Oracle Text 12.2.0.1.0 VALID XDB Oracle XML Database 12.2.0.1.0 VALID ORDIM Oracle Multimedia 12.2.0.1.0 VALID SDO Spatial 12.2.0.1.0 VALID XOQ Oracle OLAP API 12.2.0.1.0 VALID APEX Oracle Application Express 5.0.4.00.12 VALID 13 rows selected. SQL> **** Copy the TNS entries from 11g TNS home to 12c TNS home
#DELL:/u01/app/oracle/product/11.2.0.4:N <-- DELL:/u01/app/oracle/product/12.2.0.1:N
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/