- 11.2.0.4
- 12.1.0.2
- 12.2.0.1
- 18
- Database Upgrade Assistant(DBUA)
- Manual Upgrade
- Full Transportable Export Import
- Transportable Tablespace
- Datapump Export Import
|
Hostname |
gg1.soumya.com |
|
DB
SID |
Orcl12c |
|
Source
DB Version |
12.2.0.1 |
|
CDB |
Single
instance, Non CDB |
|
Source
DB Home |
/u01/app/oracle/product/12.2.0.1 |
|
Target
DB Version |
19.3.0 |
|
Target
DB Home |
/u01/app/oracle/product/19.0.0/dbhome_1 |
- Step 1.
|
Pre-requisites for Upgrade:- 1.Install Oracle Binary 19c on a different home other than 12c home.
in our case the 19c db home is /u01/app/oracle/product/19.0.0/dbhome_1 The installation of 19c can be found here. 2. Take full DB Backup before performing the upgrade. 3. Check the number of invalid objects. SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 0 If invalid objects exist, try to validate them by running utlrp.sql
from the 12c SQL>@?/rdbms/admin/utlrp.sql SQL> PURGE DBA_RECYCLEBIN; 5.Stop the running 12c listener lsnrctl stop LISTENER 6. To enable restore point , set db_recovery_file_dest location. SQL>alter system set db_recovery_file_dest_size=20G scope=both; SQL>alter system set db_recovery_file_dest='/u01/archive_log' scope=both; |
- Step 2.
|
Running the Pre-Upgrade Information Tool To check your system and database to see if it is ready for upgrade,
we use the Pre-Upgrade Information Tool (preupgrade.jar) [oracle@gg1 12.2.0.1]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1 [oracle@gg1 ~]$ java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT Report generated by Oracle Database Pre-Upgrade Information Tool
Version 19.0.0.0.0 Build: 1 on 2020-06-03T12:18:10 Upgrade-To version: 19.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: ORCL12C Container Name: orcl12c Container ID: 0 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86
64-bit Timezone File: 26 Database log mode: NOARCHIVELOG 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 Real Application Clusters
[to be upgraded] OPTION OFF Oracle Workspace Manager
[to be upgraded] VALID OLAP Analytic Workspace
[to be upgraded] VALID Oracle Label Security
[to be upgraded] VALID Oracle Database Vault
[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 Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 1. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade. None of the fixed object tables have had stats collected. Gathering statistics on fixed objects, if none
have been gathered yet, is recommended prior to upgrading. For information on managing optimizer statistics,
refer to the 12.2.0.1 Oracle Database SQL Tuning Guide.
INFORMATION ONLY ================ 2. To help you keep track of your tablespace allocations,
the following AUTOEXTEND tablespaces are expected to
successfully EXTEND during the upgrade process. Min
Size Tablespace
Size For
Upgrade ----------
---------- ----------- SYSAUX
470 MB 500 MB SYSTEM
800 MB 912 MB TEMP
32 MB
150 MB
UNDOTBS1
65 MB
439 MB
Minimum tablespace sizes for upgrade are estimates. 3. Check the Oracle Backup and Recovery User's Guide for
information on how to manage an RMAN recovery catalog schema. If you are using a version of the recovery
catalog schema that is older than that required by the RMAN client version,
then you must upgrade the catalog schema. It is good practice to have the catalog schema
the same or higher version than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database ORCL12C which are identified above as BEFORE UPGRADE
"(AUTOFIXUP)" can be resolved by executing the following
SQL>@/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/preupgrade_fixups.sql ============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 4. Upgrade the database time zone file using the DBMS_DST package. The database is using time zone file version 26
and the target 19 release ships with time zone file version 32. Oracle recommends upgrading to the desired
(latest) version of the time zone file. For more information, refer to
"Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 19
Oracle Database Globalization Support Guide.
5. To identify directory objects with symbolic links in
the path name, run $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.Recreate any directory objects listed, using path names that contain no symbolic links. Some directory object path names may currently contain symbolic links. Starting in Release 18c, symbolic links are not allowed in directory object path names used with BFILE data types, the
UTL_FILE package, or external tables. 6. (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. 7. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system 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. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database ORCL12C which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following SQL>@/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/postupgrade_fixups.sql ================== PREUPGRADE SUMMARY ================== /u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below: Before upgrade: Log into the database and execute the preupgrade fixups @/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/preupgrade_fixups.sql After the upgrade: Log into the database and execute the postupgrade fixups @/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-06-03T12:18:12 |
- Step 3.
|
Check the tablespace information reported in preupgrade log, and make
sure the autoextenstion is enabled for those tablespace. SQL> select tablespace_name,autoextensible from dba_data_Files; TABLESPACE_NAME
AUT ------------------------------ --- SYSTEM YES SYSAUX YES UNDOTBS1 YES USERS
YES |
- Step 4.
|
Run preupgrade_fixups.sql export ORACLE_SID=orcl12c [oracle@gg1 ~]$ sqlplus / as sysdba
SQL>
@/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1 Generated on: 2020-06-03
12:17:40
For Source Database: ORCL12C Source Database Version: 12.2.0.1.0 For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- ----------------------------
1. pre_fixed_objects YES None. 2. tablespaces_info NO Informational only. Further action is optional. 3. rman_recovery_version NO
Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database is not ready for upgrade. To resolve the outstanding issues, start by
reviewing the preupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done.
PL/SQL procedure successfully completed. |
- Step 5.
|
Run the DBUA and start upgrade. Please note the source database should be up and running.
[oracle@gg1 ~]$ export
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 [oracle@gg1 ~]$ export ORACLE_SID=orcl12c [oracle@gg1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@gg1 ~]$ dbua |
select the source database, and provide password for sys user,then click the “Next” button.
- Step 6.
|
Run "postupgrade_fixups.sql" to check post upgrade status.
[oracle@gg1 ~]$ export
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 [oracle@gg1 ~]$ export ORACLE_SID=orcl12c [oracle@gg1 ~]$ sqlplus / as sysdba
SQL>
@/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/postupgrade_fixups.sql
Session altered. PL/SQL procedure successfully completed.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1 Generated on: 2020-06-03
12:18:07
For Source Database: ORCL12C Source Database Version: 12.2.0.1.0 For Upgrade to Version: 19.0.0.0.0
Preup
Preupgrade Action
Issue Is Number Preupgrade Check Name Remedied
Further DBA Action ------ ------------------------ ----------
-------------------------------- 4. old_time_zones_exist
YES None. 5. dir_symlinks
YES None. 6. post_dictionary
YES None. 7. post_fixed_objects
NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database upgrade is not fully complete. To resolve the outstanding issues, start by
reviewing the postupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done.
PL/SQL procedure successfully completed. Session altered. |
- Step 7.
|
Verify the DB Version SQL> select VERSION,name,open_mode from v$instance,v$database; VERSION NAME OPEN_MODE ----------------- --------- -------------------- 19.0.0.0.0 ORCL12C READ WRITE SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_32.dat 32 0 SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 32 Re validate any invalid objects SQL>@?/rdbms/admin/utlrp.sql |
No comments:
Post a Comment