Upgrade database from 12.1.0.2 to 12.2.0.1. Upgrade from Oracle 12cR1 to 12CR2. This article includes both installation and upgrade steps with screenshots.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222
/oracle/app/oracle/product/12.1.0/dbhome_1/jdk/bin/java -jar /oracle/app/oracle/product/12.2.0.1/dbhome-1/rdbms/admin/preupgrade.jar FILE DIR /export/home/oracle/UPG122
Preupgrade generated files:
/export/home/oracle/UPG122/preupgrade.log
/export/home/oracle/UPG122/preupgrade_fixups.sql
/export/home/oracle/UPG122/postupgrade_fixups.sql
/export/home/oracle# cat /export/home/oracle/UPG122/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: TESTDB
Container Name: TESTDB
Container ID: 0
Version: 12.1.0.2.0
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Solaris[tm] OE (64-bit)
Timezone File: 18
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
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [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
---------- ---------- -------- ---------- ------
SYSAUX 550 MB DISABLED 500 MB None
SYSTEM 700 MB ENABLED 1017 MB None
TEMP 20 MB ENABLED 150 MB None
UNDOTBS1 300 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.
INFORMATION ONLY
================
+ Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
2979 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 : /archive/TESTDB
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
===================
+ Upgrade the database time zone version using the DBMS_DST package.
The database is using timezone datafile version 18 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.
SQL> @/export/home/oracle/UPG122/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: 2017-02-21 17:50:51
For Source Database: TESTDB
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
min_archive_dest_size Failed Manual fixup recommended.
PL/SQL procedure successfully completed.
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;
no rows selected
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive/TESTDB
Oldest online log sequence 195
Next log sequence to archive 197
Current log sequence 197
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> alter system set db_recovery_file_dest_size=5G scope=both;
System altered.
SQL> alter system set db_recovery_file_dest=^C
SQL> alter system set db_recovery_file_dest='/archive/TESTDB' scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
upgrade log location:
/oracle/app/oracle/cfgtoollogs/dbua/upgrade2017-02-21_05-55-20-PM/TESTDB
tail -100f Oracle_Server.log
Number of Cpus = 96
Database Name = TESTDB
DataBase Version = 12.1.0.2.0
Parallel SQL Process Count = 4
Components in [TESTDB]
Installed [CATALOG CATJAVA CATPROC JAVAVM OWM XDB XML]
Not Installed [APEX APS CONTEXT DV EM MGW ODM OLS ORDIM RAC SDO
------------------------------------------------------
Phases [0-115] Start Time:[2017_02_21 18:01:57]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [TESTDB] Files:1 Time: 44s
*************** Catalog Core SQL ***************
Serial Phase #:1 [TESTDB] Files:5 Time: 47s
Restart Phase #:2 [TESTDB] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [TESTDB] Files:19 Time: 18s
Restart Phase #:4 [TESTDB] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [TESTDB] Files:6 Time: 19s
***************** Catproc Start ****************
Serial Phase #:6 [TESTDB] Files:1 Time: 20s
***************** Catproc Types ****************
Serial Phase #:7 [TESTDB] Files:2 Time: 17s
Restart Phase #:8 [TESTDB] Files:1 Time: 1s
**************** Catproc Tables ****************
Parallel Phase #:9 [TESTDB] Files:69 Time: 26s
Restart Phase #:10 [TESTDB] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [TESTDB] Files:1 Time: 56s
Restart Phase #:12 [TESTDB] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [TESTDB] Files:97 Time: 12s
Restart Phase #:14 [TESTDB] Files:1 Time: 1s
Parallel Phase #:15 [TESTDB] Files:118 Time: 20s
Restart Phase #:16 [TESTDB] Files:1 Time: 1s
Serial Phase #:17 [TESTDB] Files:13 Time: 4s
Restart Phase #:18 [TESTDB] Files:1 Time: 1s
***************** Catproc Views ****************123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117
SQL> @ /export/home/oracle/UPG122/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: 2017-02-21 17:50:52
For Source Database: TESTDB
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
old_time_zones_exist Passed None
post_dictionary Passed None
PL/SQL procedure successfully completed.
Session altered.
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 26 0
SQL> select comp_id,status,version from dba_registry;
COMP_ID STATUS VERSION
------------------------------ ----------- ------------------------------
CATALOG VALID 12.2.0.1.0
CATPROC VALID 12.2.0.1.0
JAVAVM VALID 12.2.0.1.0
XML VALID 12.2.0.1.0
CATJAVA VALID 12.2.0.1.0
XDB VALID 12.2.0.1.0
OWM VALID 12.2.0.1.0
7 rows selected.
SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sql
Oracle Database 12.2 Post-Upgrade Status Tool 02-22-2017 08:51:39
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server VALID 12.2.0.1.0 00:14:59
JServer JAVA Virtual Machine VALID 12.2.0.1.0 00:05:47
Oracle Workspace Manager VALID 12.2.0.1.0 00:00:59
Oracle XDK VALID 12.2.0.1.0 00:01:53
Oracle XML Database VALID 12.2.0.1.0 00:02:20
Oracle Database Java Packages VALID 12.2.0.1.0 00:00:19
Final Actions 00:04:59
Post Upgrade 00:02:19
Post Compile 00:02:25
Total Upgrade Time: 00:36:46
Database time zone version is 26. It meets current release needs.
Summary Report File = /oracle/app/oracle/cfgtoollogs/dbua/upgrade2017-02-21_05-55-20-PM/TESTDB/upg_summary.log
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT DISTINCT owner, table_name
FROM dba_tab_cols
WHERE data_upgraded = 'NO'
ORDER BY 1,2;
no rows selected
SET SERVEROUTPUT ON
@utluptabdata.sql
alter database flashback off;
select * from v$restore_point;
drop restore point ;Please to add comments
No comments yet. Be the first to comment!